package xmpp.client.api.User;

import com.mysql.jdbc.Connection;
import com.mysql.jdbc.PreparedStatement;
import com.mysql.jdbc.Statement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;
import xmpp.client.api.bd.BD;
import xmpp.client.api.bd.XMPPExceptionSQL;

/**
 *
 * @author Miguel Gonzalez - Juanan Mangudo - Telecor
 */
public class UsersXMPP {
    private static final String DELETE_PROPERTY_AUTHORIZEDJIDS="DELETE FROM openfire.ofProperty WHERE ofProperty.name='admin.authorizedJIDs'";
    private static final String UPDATE_PROPERTY_AUTHORIZEDJIDS="UPDATE openfire.ofProperty SET propValue=? WHERE ofProperty.name='admin.authorizedJIDs'";
    private static final String SELECT_PROPERTY_VALUE="SELECT propValue FROM ofProperty WHERE name=?";
    private static final String SELECT_USER="SELECT name, email, creationDate, modificationDate, plainPassword, encryptedPassword FROM ofUser WHERE username=?";
    private static final String SELECT_USER_COUNT="SELECT count(*) FROM ofUser";
    private static final String SELECT_ROSTER_COUNT="SELECT count(*) FROM ofRoster WHERE username=?";
    private static final String SELECT_ROSTER_JID="SELECT jig FROM ofRoster WHERE username=?";
    private static final String INSERT_USER="INSERT INTO ofUser (username,plainPassword,encryptedPassword,name,email,creationDate,modificationDate) " +
            "VALUES (?,?,?,?,?,?,?)";
    private static final String DELETE_USER_PROPS="DELETE FROM ofUserProp WHERE username=?";
    private static final String DELETE_USER="DELETE FROM ofUser WHERE username=?";
    private static final String DELETE_GROUP_USER="DELETE FROM ofGroupUser WHERE username=?";

    private BD bd;
    private Connection connection = null;
    
    public UsersXMPP() throws XMPPExceptionSQL {
        bd = new BD();
    }
    
    public void addUser(UserXMPP user) throws XMPPExceptionSQL {
        PreparedStatement pstmt=null;
        ResultSet rs=null;
        try
        {
            if (!userExists(user.getUserName()))
            {
                pstmt = (PreparedStatement)connection.prepareStatement(INSERT_USER);
                pstmt.setString(1, user.getUserName());
                pstmt.setString(2, null);
                pstmt.setString(3, user.getPassword());
                pstmt.setString(4, user.getName());
                pstmt.setString(5, user.getEmail());
                pstmt.setString(6, user.getCreationDate());
                pstmt.setString(7, user.getModificationDate());   
                pstmt.executeUpdate();
            }
            else 
            {
                System.out.println("Error, el usuario ya existe");
            }
        }
        catch(Exception e)
        {
            System.out.println(e);
        }
         finally {
            try {

                if(pstmt != null) {
                    pstmt.close();
                }

                if(rs != null) {
                    rs.close();
                }
            } catch (SQLException sqle) {
                throw new XMPPExceptionSQL(sqle);
            }            
        }
    }
    
    //public in deleteUser(String userName)
    public int deleteUser(String userName) throws XMPPExceptionSQL {
      PreparedStatement pstmt=null;
      if (userExists(userName))
      {
            try
            {
                connection = bd.getConection();
                pstmt = (PreparedStatement)connection.prepareStatement(DELETE_USER_PROPS);
                pstmt.setString(1,userName);
                pstmt.executeUpdate();
                pstmt=(PreparedStatement)connection.prepareStatement(DELETE_USER);
                pstmt.setString(1, userName);
                pstmt.executeUpdate();
                pstmt=(PreparedStatement)connection.prepareStatement(DELETE_GROUP_USER);
                pstmt.setString(1, userName);
                pstmt.executeUpdate();  

            }
            catch(Exception e)
            {
                System.out.println("Ocurrió un error");
            }
                    finally {
                    try {
                        if(pstmt != null) {
                            pstmt.close();
                        }
                    } catch (SQLException sqle) {
                        throw new XMPPExceptionSQL(sqle);
                    }            
                }
      }
      else System.out.println("No existe");
      return 0;
    }
    
    public int vinculeUser(String idUserOne, String idUserTwo) {
        return 0;
    }
    
    
    /*public ArrayList<UserXMPP> listUsers() throws XMPPExceptionSQL {
        ArrayList<UserXMPP> usersXMPP = new ArrayList<UserXMPP>();
        
        //Abrimos la conexión
        //Obtenemos la conexión
        Connection connection = bd.getConection();
        
        ResultSet rs = null;
        try {
            //Preparamos la conexulta
            Statement stmt = (Statement) connection.createStatement();
            
            //Lanzamos la consulta
            rs = stmt.executeQuery("SELECT `username` , `name` , `email` , `encryptedPassword`, `creationDate` ,"
                    + " `modificationDate`FROM `ofUser`");
            
            //Analizamos la consulta
            while(rs.next()) {
                usersXMPP.add(new UserXMPP(rs.getString("username"), rs.getString("name"),
                        rs.getString("email"), rs.getString("encryptedPassword"),rs.getString("creationDate"), rs.getString("modificationDate")));
            }
        } catch (SQLException ex) {
            throw new XMPPExceptionSQL(ex);
        }
        
        return usersXMPP;
    }*/
    
    public boolean isAdminOpenFire(String userName) throws SQLException, XMPPExceptionSQL
    {
        PreparedStatement pstmt=null;
        ResultSet rs=null;
        boolean retorno=false;
        String query="admin.authorizedJIDs";
        String dominio=getDomain();
        userName=userName+"@"+dominio;
        try 
        {
            if (userName.equals("admin")) 
            {
                return true;
            }
            else
            {
               pstmt=(PreparedStatement)connection.prepareStatement(SELECT_PROPERTY_VALUE);
               pstmt.setString(1,query);
               rs = pstmt.executeQuery();
               if (rs.next())
               {
                   String admins[]=rs.getString(1).split(",");
                   for (String administrators:admins)
                   {
                       if (administrators.equals(userName))
                       {
                           retorno=true;
                       }
                   }
               }
               else
               {
                   retorno=false;
               } 
            }
        }
        catch(Exception e)
        {
            System.out.println(e);
        }
         finally 
        {
            try 
            {
                if(pstmt != null) 
                {
                    pstmt.close();
                }

                if(rs != null) 
                {
                    rs.close();
                }   
            } 
            catch (SQLException sqle) 
            {
                throw new XMPPExceptionSQL(sqle);
            }  
        }
        return retorno;
    }
    private String getDomain() throws XMPPExceptionSQL
    {
        PreparedStatement pstmt=null;
        ResultSet rs=null;
        String query="xmpp.domain";
        String dominio=null;
        try
        {
               pstmt=(PreparedStatement)connection.prepareStatement(SELECT_PROPERTY_VALUE);
               pstmt.setString(1,query);
               rs = pstmt.executeQuery();
               if(rs.next())
               dominio=rs.getString(1);
               else System.out.println("No hay dominio");
        }
        catch(Exception e)
        {
            System.out.println("Error encontrado en:"+e);
        }
        finally
        {
            try 
            {
                if(pstmt != null) 
                {
                    pstmt.close();
                }

                if(rs != null) 
                {
                    rs.close();
                }   
            } 
            catch (SQLException sqle) 
            {
                throw new XMPPExceptionSQL(sqle);
            } 
        }
        return dominio;
    }
            
    public void deleteAdminOpenFire(String userName) throws SQLException, XMPPExceptionSQL, UserNotFoundException
    {
        boolean borrar = false;
        PreparedStatement pstmt=null;
        ResultSet rs=null;
        String query="admin.authorizedJIDs";
        String dominio=getDomain();
        int tamaño = 0;
        try
        {
            if (userExists(userName))
            {
                    if (isAdminOpenFire(userName))
                    {
                        pstmt=(PreparedStatement)connection.prepareStatement(SELECT_PROPERTY_VALUE);
                        pstmt.setString(1, query);
                        rs = pstmt.executeQuery();
                        if (rs.next())
                        {
                            StringBuilder administradores=new StringBuilder();
                            String admins[]=rs.getString(1).split(",");
                            if (admins.length==2)
                            {
                                borrar=true;
                            } 

                                for (String administrators:admins)
                                {
                                    if (administrators.equals(userName+"@"+dominio))
                                    {

                                    }
                                    else
                                    {
                                        administradores.append(administrators+",");
                                        tamaño=administradores.length();
                                        pstmt = (PreparedStatement)connection.prepareStatement(UPDATE_PROPERTY_AUTHORIZEDJIDS);
                                        
                                        
                                    }
                                }
                            administradores.deleteCharAt(tamaño-1);
                            pstmt.setString(1, administradores.toString());
                            pstmt.executeUpdate();
                            if (borrar)
                            {
                                pstmt=(PreparedStatement)connection.prepareStatement(DELETE_PROPERTY_AUTHORIZEDJIDS);
                                pstmt.executeUpdate();
                            }
                        }
                    }
                    else 
                    {
                        throw new UserNotFoundException("El usuario no es administrador");
                    }
            }
            else
            {
                throw new UserNotFoundException("EL usuario no existe");
            }
        }
        catch(Exception e)
        {
            System.out.println("Se ha producido el error "+e);
        }
        finally 
        {
            try 
            {
                if(pstmt != null) 
                {
                    pstmt.close();
                }

                if(rs != null) 
                {
                    rs.close();
                }
                
            } 
            catch (SQLException sqle) 
            {
                throw new XMPPExceptionSQL(sqle);
            }  
        }

    }
    public boolean userExists(String userName) throws XMPPExceptionSQL
    {
      PreparedStatement pstmt=null;
      ResultSet rs=null;
      try
      {
            pstmt = (PreparedStatement)connection.prepareStatement(SELECT_USER);
            pstmt.setString(1, userName);
            rs = pstmt.executeQuery();
            if (rs.next()) 
            {
                return true;
            }
            else return false;
      }
      catch(Exception e)
      {
          System.out.println(e);
      }
              finally 
        {
            try 
            {
                if(pstmt != null) 
                {
                    pstmt.close();
                }

                if(rs != null) 
                {
                    rs.close();
                }      
            } 
            catch (SQLException sqle) 
            {
                throw new XMPPExceptionSQL(sqle);
            }  
        }
      return false;
    }
    
    public UserXMPP getUser(String userName) throws XMPPExceptionSQL
    {
      PreparedStatement pstmt=null;
      ResultSet rs=null;
      UserXMPP user = null;
      try
      {
            pstmt = (PreparedStatement)connection.prepareStatement(SELECT_USER);
            pstmt.setString(1,userName);
            System.out.println("Llego antes del resultset");
            rs=pstmt.executeQuery();
            System.out.println("Estoy despues del resutlsert");
            if (rs.next())
            {
                String name=rs.getString(1);
                String email=rs.getString(2);
                String creationDate=rs.getString(3);
                String modificationDate=rs.getString(4);
                String plainPassword=rs.getString(5);
                String encryptedPassword=rs.getString(6);
                user=new UserXMPP(userName, name, email, plainPassword, encryptedPassword, creationDate, modificationDate);
            }
      }
      catch(Exception e)
      {
          System.out.println("Ha ocurrido el error: "+e+" en getUser");
      }
              finally 
        {
            try 
            {
                if(pstmt != null) 
                {
                    pstmt.close();
                }

                if(rs != null) 
                {
                    rs.close();
                }
                
            } 
            catch (SQLException sqle) 
            {
                throw new XMPPExceptionSQL(sqle);
            }  
        }
        return user;
    }
    
    public void setConnection(Connection connection) {
        bd.setConnection(connection);
    }
    
    public ArrayList<String> getFriends(String userName) throws XMPPExceptionSQL
    {
        ArrayList<String> friendList=new ArrayList<String>();
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        try {
            pstmt = (PreparedStatement) connection.prepareStatement(SELECT_ROSTER_JID);
            pstmt.setString(1, userName);
            rs = pstmt.executeQuery();
            if (rs.next()) {
                String listaAmigos = rs.getString(1);

                String[] amigos = listaAmigos.split(",");
                for(String amigo : amigos) 
                {
                    friendList.add(amigo);
                }
            }
        }
        catch (SQLException e) {
            throw new XMPPExceptionSQL(e);
        }
        finally {
            try {
                if(pstmt != null) {
                    pstmt.close();
                }

                if(rs != null) {
                    rs.close();
                }
                
            } catch (SQLException sqle) {
                throw new XMPPExceptionSQL(sqle);
            }            
        }
           return friendList;
    }
    
    //public addFriend(String userName, String userNameFriend) throws...
    
    //public delFriend(String userName, String userNameFrined) throws...
    
    public boolean isFriend(String userName, String userNameFriend) throws XMPPExceptionSQL 
    {
        ArrayList<String> listaAmigos=new ArrayList<String>();
        listaAmigos=getFriends(userName);
        for(String amigo:(ArrayList<String>)listaAmigos)
        {
            if(userNameFriend.equals(amigo)) return true;
        }
        
        return false;
    }
    
    public int countFriends(String userName) throws XMPPExceptionSQL
    {
        int friendsNumber=0;
                PreparedStatement pstmt = null;
        ResultSet rs = null;
        try 
        {
            
            pstmt = (PreparedStatement) connection.prepareStatement(SELECT_ROSTER_COUNT);
            rs = pstmt.executeQuery();
            if (rs.next()) 
            {
                friendsNumber = rs.getInt(1);
            }
        }
        catch (SQLException e) 
        {
            throw new XMPPExceptionSQL(e);
        }
        finally 
        {
            try 
            {
                if(pstmt != null) 
                {
                    pstmt.close();
                }

                if(rs != null) 
                {
                    rs.close();
                }
                
            } 
            catch (SQLException sqle) 
            {
                throw new XMPPExceptionSQL(sqle);
            }  
        }
        return friendsNumber;
    }
    
    public int countUsers() throws XMPPExceptionSQL
    {
        int usersNumber=0;
                PreparedStatement pstmt = null;
        ResultSet rs = null;
        try 
        {
            
            pstmt = (PreparedStatement) connection.prepareStatement(SELECT_USER_COUNT);
            rs = pstmt.executeQuery();
            if (rs.next()) 
            {
                usersNumber = rs.getInt(1);
            }
        }
        catch (SQLException e) 
        {
            throw new XMPPExceptionSQL(e);
        }
        finally 
        {
            try 
            {
                if(pstmt != null) 
                {
                    pstmt.close();
                }

                if(rs != null) 
                {
                    rs.close();
                }        
            } 
            catch (SQLException sqle) 
            {
                throw new XMPPExceptionSQL(sqle);
            }  
        }
        return usersNumber;
    }
    
    
    
    public void openConnection(String userName, String passwordName, String host, String bdName) throws XMPPExceptionSQL 
    {
        bd.openConnection(userName, passwordName, host, bdName);
        connection = bd.getConection();
    }
    
    public void closeConnection() throws XMPPExceptionSQL 
    {
            bd.closeConnection();       
    }
}
